using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace nmspDataWEB
{

    /// <summary>
    /// ADO.NET data access using the SQL Server Managed Provider.
    /// </summary>
    public class Database : IDisposable
    {
        // connection to data source
        private SqlConnection con;

        //*******************************************************
        //Destructor ********************************************
        //*******************************************************
        ~Database()
        {
            Close();
            Dispose();
        }

        //*******************************************************
        //Se retorna la cadena de conexion a la base de datos del
        //archivo Web.Config. En caso que no pueda leerlo se toma
        //una conexion hardcodeada en la base
        //*******************************************************
        private string getConectionString()
        {
            string str_conexion;

            if ((ConfigurationManager.ConnectionStrings["autoGeneratedConnection"].ConnectionString) == null) 
                str_conexion = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
            else
                str_conexion = ConfigurationManager.ConnectionStrings["autoGeneratedConnection"].ConnectionString;

            

            return str_conexion;
        }

        // ******************
        // ******************
        private void Open()
        {

            // open connection
            if (con == null)
            {
                string str_conexion = getConectionString();
                this.con = new SqlConnection(str_conexion);
                this.con.Open();
            }
            else
            {
                if (this.con.State.Equals(ConnectionState.Closed))
                    this.con.Open();
            }                
        }

        // ******************
        // ******************
        private void Close()
        {
            if (con != null)
                if(this.con.State!=ConnectionState.Closed)
                    con.Close();
        }

        // ******************
        // ******************
        public void Dispose()
        {
            // aseguramos de matar la conexion.
            if (con != null)
            {
                con.Dispose();
                con = null;
            }                
        }

        // ***********************************************************
        // ***********************************************************
        public DataTable GetDataTable(string Sql,string ConnectString)
        {

            int rows = 0;
            DataTable dt = new DataTable();

            try
            {
                // En el open se realizan alguos controles
                Open();

                SqlDataAdapter da = new SqlDataAdapter(Sql,ConnectString);
                rows = da.Fill(dt);        
                return dt;
            }
            catch (Exception e)
            {
                Console.Write(e.Message);
                return dt;
            }
            finally
            {
                Close();
            }
        }

        // **************************************************************
        // **************************************************************
        public DataTable GetDataTable(string Sql)
        {

            int rows = 0;
            DataTable dt = new DataTable();

            try
            {
                // En el open se realizan alguos controles
                Open();

                SqlDataAdapter da = new SqlDataAdapter(Sql, this.con);
                rows = da.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                Console.Write(e.Message);
                return dt;
            }
            finally
            {
                Close();
            }
            
        }

        //**************************************************************
        // solamente para insert y updates *****************************
        // deberia retornar la cantidad de filas afectadas *************
        //**************************************************************
        public int ejecutarStatement(string sql)
        {
            int retornar_filas_modificadas = 0;
            
            try
            {
                // Abrir la coneccion - de todos modos verifica el estado
                Open();

                SqlCommand commando = new SqlCommand(sql, this.con);
                retornar_filas_modificadas = commando.ExecuteNonQuery();

                return retornar_filas_modificadas;
            }
            catch(Exception e)
            { 
                Console.Write(e.Message);
                return retornar_filas_modificadas;
            }
            finally
            {
                Close();
            }
                
        }
    }
}  // namespace WEB{